import os
import datetime
import Core.Config as Config
from Factors_Systematic.SysFactors_CapitalFlow import *
from Factors_Systematic.SysFactors_ExternalFiles import *
from DataProcess.Download_Stock_Dividend import *
from DataProcess.Download_Stock_Performance_Express_Profit_Notice import *


# blocktrader大宗交易
def Download_BlockTrade_Automatic(database, datetime2):
    #
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_block_trade")
    maxDate = maxDate[0][0]
    print("Last Update by", maxDate)
    #
    datetime1 = maxDate + datetime.timedelta(days=1)
    #
    if datetime1 > datetime2.date():
        print("Block Trade Data is Up to Date, Unnecessary to Update")
    else:
        dfDetails = Download_BlockTrade(database, datetime1, datetime2)
    #
    # maxDate = database.ExecuteSQL("factor", "select max(date) from factor.a_sys_factor where name='BlockTrade_Amt'")
    # maxDate = maxDate[0][0]
    # if maxDate > datetime1:
    #     pass
    # else:
    #     datetime1 = maxDate


def Download_BlockTrade(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    # ---Request Data---
    # price交易价 rate_discount 折价率 volumn成交量 amount成交额
    strParams = 'startdate=' + sDateTime1 +';enddate=' + sDateTime2 + \
                ';sectorid=a001010100000000;' \
                'field=trade_date,price,rate_discount,volume,amount,wind_code,close'
    blocktrader = w.wset("blocktraderecord", strParams)
    # blocktrader = w.wset("blocktraderecord","startdate={};enddate={};sectorid=a001010100000000;field=trade_date,price,rate_discount,volume,amount".format(begindate, enddate))

    if len(blocktrader.Data)==0:
        print("No data, Download_BlockTrade", datetime1,  datetime2)
        return

    # ---Build Dataframe---
    trade_date = pd.DataFrame(data=blocktrader.Data[0], columns=["Date"])
    dfPrice = pd.DataFrame(data=blocktrader.Data[1], columns=["Price"])
    rate_discount = pd.DataFrame(data=blocktrader.Data[2], columns=["Rate_Discount"])
    volumn = pd.DataFrame(data=blocktrader.Data[3], columns=["Volume"])
    amount = pd.DataFrame(data=blocktrader.Data[4], columns=["Amount"])
    dfSymbol = pd.DataFrame(data=blocktrader.Data[5], columns=["Symbol"])
    df_close = pd.DataFrame(data=blocktrader.Data[6], columns=["Close"])
    #
    df_blocktrades = pd.concat([trade_date, dfPrice, rate_discount, volumn, amount, dfSymbol, df_close], axis=1)
    # print(df_blocktrades)
    print(df_blocktrades.tail(10))

    # ---Save To Database---
    columns = df_blocktrades.columns
    newDocuments = []
    for index, row in df_blocktrades.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
        #
        newDocument["DateTime"] = row["Date"]
        # newDocument["Key2"] = newDocument["Name"] + "_" + datetime.datetime.strftime(newDocument["Date"], '%Y-%m-%d')
        newDocuments.append(newDocument)
    #
    database.Insert_Many("stock", "block_trade", newDocuments)
    print("Inserted Block Trade from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df_blocktrades


def Download_IPO(database, datetime1, datetime2):
    # 因为会出现网下发行日期和上市日期相隔较远的情况
    # 所以调用两遍 Download_IPO 函数
    Download_IPO_Sub(database, datetime1, datetime2, datetype="prospectus")
    # Download_IPO_Sub(database, datetime1, datetime2, datetype="online")
    Download_IPO_Sub(database, datetime1, datetime2, datetype="listing")
    pass


def Download_IPO_Sub(database, datetime1, datetime2, datetype="prospectus"):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    field_map= {}
    field_map["wind_code"] = "Symbol" # "代码"
    field_map["sec_name"] = "Name" # "名称"
    field_map["prospectus_date"] = "Announce_Date" # "招股日期"
    field_map["online_issue_date"] = "Issue_Date" # "网上发行日期"
    field_map["listing_date"] = "IPO_Date" # "上市日期"
    field_map["issue_price"] = "IPO_Price" # "发行价格"
    field_map["ipo_pe"] = "IPO_PE" # "发行市盈率"
    field_map["industrype_ttm"] = "Industry_PE_TTM" # "行业PE(近1月,TTM)"
    field_map["issue_amount"] = "Issue_Volume" # "总计"
    field_map["expected_raised_fund"] = "Issue_Amount_Fore" # "预计募资(上市公司)"
    field_map["actual_raised_fund"] = "Issue_Amount" # "募资总额(上市公司)"
    field_map["csrc_industry"] = "CSRC_Industry" # "证监会行业(2012版)"
    # fieldMap[""] = "Wind_Industry" # "Wind行业"

    wind_fields = []
    db_fields = []
    for wind_field, db_field in field_map.items():
        wind_fields.append(wind_field)
        db_fields.append(db_field)

    str_fields = ",".join(wind_fields)

    # ---Request Data---
    # datetype :
    # prospectus 招股日期,
    # online 网上发行日期
    # offline 网下发行日期
    # listing 上市日 有些股票只有上市日，其他字段都是空的
    strParams = 'startdate=' + sDateTime1 +';enddate=' + sDateTime2 + ";"\
                'datetype=' + datetype + ';' \
                'board=0;' \
                'field=' + str_fields
    data_ipo = w.wset("newstockissueinfo", strParams)

    # ---Build Dataframe---
    df = pd.DataFrame()
    for i in range(len(db_fields)):
        db_field = db_fields[i]
        df_tmp = pd.DataFrame(data=data_ipo.Data[i], columns=[db_field])
        # print(df_tmp)
        if df.empty:
            df = df_tmp
        else:
            df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)

    #
    # df_blocktrades = pd.concat([trade_date, dfPrice, rate_discount, volumn, amount, dfSymbol, df_close], axis=1)
    # print(df_blocktrades)
    df.sort_values(by="Announce_Date", inplace=True)
    print(df[["Symbol", "Name", "IPO_Date", "Issue_Amount"]].tail(10))
    # df.to_csv("d://ttt.csv")

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
        #
        newDocument["Date"] = newDocument["Announce_Date"]
        newDocument["DateTime"] = newDocument["Announce_Date"]

        if str(newDocument["Announce_Date"]) != "NaT":
            newDocument["Key2"] = newDocument["Symbol"] + "_" + Gadget.ToDateString(newDocument["Announce_Date"])
        else:
            newDocument["Key2"] = newDocument["Symbol"]
        #
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "ipo", {}, newDocuments)
    print("Upsert IPO Info from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df


def Download_SEO(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    field_map= {}
    field_map["wind_code"] = "Symbol" # "代码"
    field_map["sec_name"] = "Name" # "名称"
    #
    field_map["issue_date"] = "Issue_Date" # "发行日期"
    field_map["placement_list_date"] = "Listing_Date" # "定增股份上市日" # 公开发行的增发无此字段
    field_map["initial_announcement_date"] = "plan_announce_date" # "初始预案公告日"
    field_map["price_base_date"] = "price_base_date" # "定价基准日"
    field_map["holders_meeting_date"] = "holder_meeting_Announce_Date" # "股东大会公告日"
    field_map["ipo_issue_date"] = "SEO_Announce_Date" # "增发公告日"
    field_map["unlocking_time"] = "UnRestrict_Date" # "限售解禁日" # 公开增发的股票上市日即解禁日
    #
    field_map["offering_type"] = "offering_type" # 发行方式
    field_map["investor"] = "offering_to" # 投资者
    field_map["purchase_method"] = "payment_method" # "认购方式"
    field_map["seo_type"] = "seo_type" # "增发类型"
    field_map["seo_purpose"] = "seo_purpose" # "增发目的"
    field_map["price_base_date_type"] = "price_base_date_type" # "定价基准日类型"
    field_map["price_rules"] = "price_rules" # "定价方式"
    field_map["sec_type"] = "sec_type" # "证券类型"

    field_map["discount_rate"] = "discount_rate" # "折扣率(%)"
    field_map["practice_price_bp_proportion"] = "practice_price_to_base_ratio" # "实施价格相对于基准价格-%"
    field_map["plan_price_bp_proportion"] = "plan_price_to_base_ratio" # "预案价格相对于基准价格-%"
    field_map["major_holder_subscription"] = "Is_Major_ShareHolder_Subscribe" # "是否大股东认购"
    field_map["major_holder_sub_proportion"] = "Major_ShareHolder_Subscribe_Ratio" # "大股东认购比例(%)"
    field_map["major_holder_sub_approach"] = "Major_ShareHolder_Payment_Method" # "大股东认购方式"
    #
    field_map["issue_price"] = "Issue_Price" # "发行价格"
    field_map["issue_day_close"] = "Issue_Day_Close" # "增发日收盘价"
    field_map["seo_volume"] = "Issue_Volume" # "增发数量(万股)"
    field_map["predicted_amount_raised"] = "Issue_Amount_Fore" # "预计募集资金-亿"
    field_map["actual_amount_raised"] = "Issue_Amount" # "实际募资总额(亿元)"
    field_map["capital_amount_raised"] = "Asset_Amount" # "其中使用资产募集规模(亿元)"
    field_map["currency_amount_raised"] = "Cash_Amount" # "其中使用货币募集规模(亿元)"
    field_map["net_amount_raised"] = "Issue_Amount_Net" # "实际募资净额(亿元)"


    wind_fields = []
    db_fields = []
    for wind_field, db_field in field_map.items():
        wind_fields.append(wind_field)
        db_fields.append(db_field)

    str_fields = ",".join(wind_fields)

    # ---Request Data---
    # datetype :
    # issue_date 发行日,
    # ipo_issue_date seo披露日期
    strParams = 'startdate=' + sDateTime1 +';enddate=' + sDateTime2 + ";"\
                'sectorId=a001010100000000;' \
                'datetype=ipo_issue_date'
    wind_data = w.wset("seoimplementation", strParams)

    if len(wind_data.Data) == 0:
        print("No SEO Info from", datetime1, "to", datetime2)
        return

    # ---Build Dataframe---
    df = pd.DataFrame()
    for i in range(len(wind_data.Fields)):
        wind_field = wind_data.Fields[i]
        if wind_field not in field_map:
            print("No Map", wind_field)
            continue
        db_field = field_map[wind_field]
        df_tmp = pd.DataFrame(data=wind_data.Data[i], columns=[db_field])
        # print(df_tmp)
        if df.empty:
            df = df_tmp
        else:
            df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)

    #
    print(df[["Name","Issue_Date", "Listing_Date"]].head(10))
    # df.to_csv("d://seo.csv")

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
            if value == "":
                newDocument[field] = None
        #
        if newDocument["Symbol"] == "601899.SH":
            a = 0
        #
        # newDocument["Is_Private"] = True
        if newDocument["Is_Major_ShareHolder_Subscribe"] == "是":
            newDocument["Is_Major_ShareHolder_Subscribe"] = True
        elif newDocument["Is_Major_ShareHolder_Subscribe"] == "否":
            newDocument["Is_Major_ShareHolder_Subscribe"] = False
        else:
            newDocument["Is_Major_ShareHolder_Subscribe"] = None

        # 公开定增没有 placement_list_date 字段，用 unlocking_time字段代表上市
        if newDocument["offering_type"] == "公开发行":
            newDocument["Listing_Date"] = newDocument["UnRestrict_Date"]

        #
        newDocument["Date"] = newDocument["SEO_Announce_Date"]
        newDocument["DateTime"] = newDocument["SEO_Announce_Date"]
        newDocument["Key2"] = newDocument["Symbol"] + "_" \
                           + Gadget.ToDateString(newDocument["plan_announce_date"]) + "_" \
                           + str(newDocument["Issue_Price"]) + "_" \
                           + str(newDocument["offering_to"]) + "_" \
                           + str(newDocument["seo_purpose"])
        # 已没有重复
        #
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "seo", {}, newDocuments)
    print("Upsert SEO Info from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df

# 有13个Listing data 0001-01-01 的数据
# API数据中多出3次保千里（其中一次 Listing date 0001-01-01） 600074.SH
def Fix_SEO(database):
    pass
    newDocuments = []
    # 1
    newDocument = {"key2": "600284.SH_2007-09-28_10.64_全体投资者_None", "listing_date": datetime.datetime(2008,7,8)}
    newDocuments.append(newDocument)
    #2
    newDocument = {"key2": "000655.SZ_2001-04-05_9.1_全体投资者_None", "listing_date": datetime.datetime(2002,12,17)}
    newDocuments.append(newDocument)
    #3
    newDocument = {"key2": "000517.SZ_2001-06-27_17.26_全体投资者_None", "listing_date": datetime.datetime(2002,5,23)}
    newDocuments.append(newDocument)
    #4
    newDocument = {"key2": "600606.SH_2001-01-20_11.55_全体投资者_None", "listing_date": datetime.datetime(2002,4,23)}
    newDocuments.append(newDocument)
    #5
    newDocument = {"key2": "000806.SZ_2000-08-31_11.7_全体投资者_None", "listing_date": datetime.datetime(2002, 3, 22)}
    newDocuments.append(newDocument)
    #6
    newDocument = {"key2": "000735.SZ_2000-11-25_4.99_全体投资者_None", "listing_date": datetime.datetime(2002, 2, 7)}
    newDocuments.append(newDocument)
    #7
    newDocument = {"key2": "600611.SH_2001-03-10_6.8_A股流通股_项目融资", "listing_date": datetime.datetime(2001, 12, 28)}
    newDocuments.append(newDocument)
    #8
    newDocument = {"key2": "600295.SH_2000-08-21_16.8_全体投资者_None", "listing_date": datetime.datetime(2001, 4, 26)}
    newDocuments.append(newDocument)
    #9
    newDocument = {"key2": "600272.SH_2000-08-10_9.6_全体投资者_None", "listing_date": datetime.datetime(2001, 2, 28)}
    newDocuments.append(newDocument)
    #10
    newDocument = {"key2": "000050.SZ_2000-08-10_22.0_全体投资者_None", "listing_date": datetime.datetime(2001, 2, 23)}
    newDocuments.append(newDocument)
    #11
    newDocument = {"key2": "600186.SH_2000-08-14_9.45_全体投资者_None", "listing_date": datetime.datetime(2001, 2, 14)}
    newDocuments.append(newDocument)
    #12
    newDocument = {"key2": "000636.SZ_1999-10-19_28.5_全体投资者_None", "listing_date": datetime.datetime(2000, 5, 30)}
    newDocuments.append(newDocument)

    #
    for document in newDocuments:
        database.Update("stock", "seo", target={"key2":document["key2"]}, document={"listing_date": document["listing_date"]})
        a = 0


def Download_Private_SEO(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    field_map= {}
    field_map["wind_code"] = "Symbol" # "代码"
    field_map["sec_name"] = "Name" # "名称"
    field_map["seo_announcedate"] = "Announce_Date" # "股东大会公告日"
    field_map["issue_date"] = "Issue_Date" # "发行日期"
    field_map["list_date"] = "GoPublic_Date" # "定增股份上市日"
    field_map["release_date"] = "UnRestrict_Date" # "限售解禁日"

    field_map["seo_price"] = "SEO_Price" # "发行价格"
    field_map["seo_vol"] = "Issue_Volume" # "增发数量(万股)"
    field_map["planed_raised_funds"] = "Issue_Amount_Fore" # "预计募集资金总额(亿元)"
    field_map["raised_funds"] = "Issue_Amount" # "实际募资总额(亿元)"

    field_map["ms_method"] = "Payment" # "认购方式"
    field_map["ms_ratio"] = "Major_ShareHolder_Subscribe" # "大股东认购比例(%)"
    field_map["discount_rate"] = "Discount" # "折扣率(%)"
    # field_map["csrc_industry"] = "CSRC_Industry" # "证监会行业(2012版)"
    # fieldMap[""] = "Wind_Industry" # "Wind行业"

    wind_fields = []
    db_fields = []
    for wind_field, db_field in field_map.items():
        wind_fields.append(wind_field)
        db_fields.append(db_field)

    str_fields = ",".join(wind_fields)

    # ---Request Data---
    # datetype : prospectus 招股日期, online 网上发行日期
    strParams = 'startdate=' + sDateTime1 +';enddate=' + sDateTime2 + ";"\
                'sectorId=a001010100000000;' \
                'field=' + str_fields
    data_seo = w.wset("privateplacement", strParams)

    if len(data_seo.Data) == 0:
        print("No SEO Info from", datetime1, "to", datetime2)
        return

    # ---Build Dataframe---
    df = pd.DataFrame()
    for i in range(len(db_fields)):
        db_field = db_fields[i]
        df_tmp = pd.DataFrame(data=data_seo.Data[i], columns=[db_field])
        # print(df_tmp)
        if df.empty:
            df = df_tmp
        else:
            df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)

    #
    print(df.tail(10))

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
            if value == "":
                newDocument[field] = None
        #
        newDocument["Is_Private"] = True
        newDocument["Date"] = newDocument["Announce_Date"]
        newDocument["DateTime"] = newDocument["Announce_Date"]
        newDocument["Key2"] = newDocument["Symbol"] + "_" \
                           + Gadget.ToDateString(newDocument["Announce_Date"]) + "_" \
                           + Gadget.ToDateString(newDocument["Issue_Date"]) + "_" + str(newDocument["SEO_Price"])
        # 仍然有少量重复
        #
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "private_seo", {}, newDocuments)
    print("Upsert Private SEO Info from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df


# 配股规模
def Download_RightIssue(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    field_map = {}
    field_map["wind_code"] = "Symbol"  # "代码"
    field_map["sec_name"] = "Name"  # "名称"

    # 预案字段
    # field_map["preplan_anncedate"] = "Announce_Date"  # "预案公告日"
    # #
    # field_map["price_range"] = "Price_Range"  # "价格范围"
    # field_map["rights_issue_ratio"] = "Rights_Issue_Ratio"  # "配股比例"
    # field_map["planned_volume"] = "Issue_Volume_Fore"  # "计划配股数量（万）"
    # field_map["predicted_amount_raised"] = "Issue_Amount_Fore"  # "预计募集资金（亿元）"

    # 实施字段
    field_map["rights_issue_anncedate"] = "Announce_Date"  # "配股公告日"
    field_map["record_date"] = "Record_Date"  # "股权登记日"
    field_map["ex_rights_date"] = "EX_Rights_Date"  # "除权日" # 真正缴款日应介于 登记日和除权日之间
    field_map["listing_date"] = "Listing_Date"  # "上市日期" # wind 融资统计按照上市日期进行月度归类
    #
    field_map["rights_issue_price"] = "rights_issue_price"  # "配股价格"
    field_map["rights_issue_ratio"] = "Rights_Issue_Ratio"  # "配股比例"
    field_map["subscription_ratio"] = "Subscription_Ratio"  # "认购比例"
    #
    field_map["planned_volume"] = "Issue_Volume_Fore"  # "计划配股数量（万）"
    field_map["predicted_amount_raised"] = "Issue_Amount_Fore"  # "预计募集资金（亿元）"
    field_map["actual_volume"] = "Issue_Volume"  # "实际认购数量 万股"
    field_map["total_funds_raised"] = "Issue_Amount"  # "募资合计"
    field_map["actual_funds_raised"] = "Issue_Amount_Net"  # "实际募资"

    # fieldMap[""] = "Wind_Industry" # "Wind行业"

    wind_fields = []
    db_fields = []
    for wind_field, db_field in field_map.items():
        wind_fields.append(wind_field)
        db_fields.append(db_field)

    str_fields = ",".join(wind_fields)

    # ---Request Data---
    # datetype : prospectus 招股日期, online 网上发行日期
    strParams = 'startdate=' + sDateTime1 + ';enddate=' + sDateTime2 + ";" \
                                                                       'sectorid=a001010100000000;' \
                                                                       'windcode='
    wind_data = w.wset("rightsissueimplementation", strParams)

    # ---Build Dataframe---
    df = pd.DataFrame()
    for i in range(len(wind_data.Fields)):
        wind_field = wind_data.Fields[i]
        if wind_field not in field_map:
            print("No Map", wind_field)
            continue
        db_field = field_map[wind_field]
        df_tmp = pd.DataFrame(data=wind_data.Data[i], columns=[db_field])
        # print(df_tmp)
        if df.empty:
            df = df_tmp
        else:
            df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)

    #
    df.sort_values(by="Announce_Date", inplace=True)
    print(df[["Symbol", "Name", "Announce_Date", "Issue_Amount"]].tail(10))
    # df.to_csv("d://ttt.csv")

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
        #
        newDocument["Date"] = newDocument["Announce_Date"]
        newDocument["DateTime"] = newDocument["Announce_Date"]
        newDocument["Key2"] = newDocument["Symbol"] + "_" + Gadget.ToDateString(newDocument["Announce_Date"])
        #
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "rights_issue", {}, newDocuments)
    print("Upsert rights_issue Info from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df


# 优先股
# 2020-8-24 优化了代码，建议推广
def Download_PreferredStock(database, datetime1, datetime2):
    field_map = {}
    field_map["wind_code"] = "Symbol"  # "证券代码"
    field_map["sec_name"] = "Name"  # "证券简称"
    field_map["issuer"] = "Issuer"  # "发行人"

    # field_map["progress"] = "progress"  # "方案进度"
    field_map["plan_announce_date"] = "Plan_Announce_Date"  # "预案公告日"
    field_map["issue_date"] = "Listing_Date"  # "上市日期" # 上市日 / wind接口字段叫发行日
    # field_map["IEC_Announce_Date"] = "IEC_Announce_Date"  # 发审委审核日 / 接口不存在该字段
    field_map["dy_type"] = "Interest_Rate_Type"  # "股息率类型"
    field_map["coupon_dy"] = "Coupon_Rate"  # 票面股息率

    # field_map["can_trading"] = "can_trading"  # 是否可交易 / Excel 不存在该字段
    # field_map["is_public"] = "is_public"  # 是否公开发行 / Excel 不存在该字段
    field_map["coupon_dy"] = "Coupon_Rate"  # 票面股息率

    field_map["initial_price"] = "Initial_Conversion_Price"  # 最初转让价格
    field_map["total"] = "Issue_Amount"  # 募集资金总额(元) 转换成亿元
    field_map["net"] = "Issue_Amount_Net"  # 募集资金净额(元) 转换成亿元

    field_map["callable"] = "Callable"  # 是否可赎回
    field_map["convertible"] = "Convertible"  # 是否可转股
    #
    # document["CSRC_Industry"] = row_data[headerIndexByName["所属证监会行业"]] 接口不存在该字段
    # document["Wind_Industry"] = row_data[headerIndexByName["所属Wind行业"]] 接口不存在该字段
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    # type : plan 预案日期, issue 发行日期
    date_type = "plan"
    strParams = 'startdate=' + sDateTime1 + ';enddate=' + sDateTime2 + "type=" + date_type
    wind_data = w.wset("pereferredstock", strParams)

    #
    data_cache = {}
    for i in range(len(wind_data.Fields)):
        wind_field = wind_data.Fields[i]
        if wind_field not in field_map:
            print("No Map", wind_field)
            continue
        db_field = field_map[wind_field]
        data_cache[db_field] = wind_data.Data[i]

    df = pd.DataFrame(data_cache)
    # print(df)
    #
    df["Issue_Amount"] = df["Issue_Amount"] * 0.0001 * 0.0001 # 元转换为亿元
    df["Issue_Amount_Net"] = df["Issue_Amount_Net"] * 0.0001 * 0.0001 # 元转换为亿元
    df["Date"] = df["Plan_Announce_Date"]
    df["DateTime"] = df["Plan_Announce_Date"]
    df["key2"] = df["Symbol"] + "_" + df["Plan_Announce_Date"].apply(lambda x : x.strftime('%Y-%m-%d'))
    #
    database.SaveDataFrame("financial_data", "stock_preferred_stock", df)
    print("Upsert Preferred Stock from", datetime1, "to", datetime2, "#", len(df))


# 转债，含交债
def Download_ConvertibleBond(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    field_map = {}
    field_map["_companycode"] = "Symbol"  # "代码"
    field_map["name"] = "Name"  # "名称"
    field_map["bond_code"] = "Bond_Symbol"  # "转债代码"
    field_map["bond_name"] = "bond_name"  # "转债名称"
    field_map["issue_type"] = "issue_type"  # "发行方式"

    # 实施字段
    field_map["listing_date"] = "Listing_Date"  # "上市日期" # # wind 融资统计按照上市日期进行月度归类
    field_map["interest_start_date"] = "Interest_Start_Date"  # "起息日期" # 相当于issue_date,发行日
    field_map["interest_end_date"] = "Maturity_Date"  # "到期日期"
    field_map["conversion_start_date"] = "conversion_start_date"  # 转股起始日
    #
    field_map["issue_year"] = "term"  # "期限 年"
    field_map["coupon_rate"] = "coupon_rate"  # "票面利率 %"
    field_map["coupon_compensation_rate"] = "coupon_compensation_rate"  # "补偿利率 %"
    field_map["interest_rate_type"] = "interest_rate_type"  # "利率类型"
    field_map["coupon_frequency"] = "coupon_frequency"  # "付息频率"
    #
    field_map["initial_conversion_price"] = "initial_conversion_price"  # 初始转股价格
    field_map["latest_conversion_price"] = "latest_conversion_price"  # "最新转股价格
    field_map["credit_rating"] = "credit_rating"  # 信用等级
    field_map["issue_size"] = "Issue_Amount"  # "发行规模 亿元"

    # fieldMap[""] = "Wind_Industry" # "Wind行业"

    wind_fields = []
    db_fields = []
    for wind_field, db_field in field_map.items():
        wind_fields.append(wind_field)
        db_fields.append(db_field)

    str_fields = ",".join(wind_fields)

    # ---Request Data---
    # datetype : prospectus 招股日期, online 网上发行日期
    strParams = 'startdate=' + sDateTime1 + ';enddate=' + sDateTime2
    wind_data = w.wset("cbissue", strParams)

    # ---Build Dataframe---
    df = pd.DataFrame()
    for i in range(len(wind_data.Fields)):
        wind_field = wind_data.Fields[i]
        if wind_field not in field_map:
            print("No Map", wind_field)
            continue
        db_field = field_map[wind_field]
        df_tmp = pd.DataFrame(data=wind_data.Data[i], columns=[db_field])
        # print(df_tmp)
        if df.empty:
            df = df_tmp
        else:
            df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)

    #
    df.sort_values(by="Interest_Start_Date", inplace=True)
    print(df[["Symbol", "Name", "Interest_Start_Date", "Listing_Date", "Issue_Amount"]].tail(10))
    # df.to_csv("d://ttt.csv")

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
        #
        newDocument["Date"] = newDocument["Listing_Date"]
        newDocument["DateTime"] = newDocument["Listing_Date"]
        newDocument["Key2"] = newDocument["Symbol"] + "_" + newDocument["Bond_Symbol"]
        #
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "convertible_bond", {}, newDocuments)
    print("Upsert CB(EB) issue Info from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df


def Download_Stock_Connect_Detail(database, datetime1, datetime2):

    def Download_Stock_Connect_One_Day(database, datetime2, trade_board):
        sDateTime2 = Gadget.ToDateString(datetime2)
        #
        field_map = {}
        field_map["wind_code"] = "Symbol"  # "代码"
        field_map["sec_name"] = "Name"  # "名称"
        field_map["hold_stocks"] = "holding_volume"  # "持股数量"
        field_map["change_holdstocks"] = "holding_volume_change"  # "持股数量"
        field_map["holding_marketvalue"] = "holding_market_value"  # "持股市值"
        field_map["publish_ratio"] = "holding_ratio"  # "持股比例"
        field_map["calculate_ratio"] = "holding_ratio_calculated"  # "持股比例（计算）"
        field_map["float_shareratio"] = "holding_to_float_share_ratio"  # "持股占流通市值比例"
        #
        strParams = 'date=' + sDateTime2 + ';tradeboard=' + trade_board
        wind_data = w.wset("shszhkstockholdings", strParams)
        #
        wind_fields = []
        db_fields = []
        for wind_field, db_field in field_map.items():
            wind_fields.append(wind_field)
            db_fields.append(db_field)

        # ---Build Dataframe---
        df = pd.DataFrame()
        for i in range(len(wind_data.Fields)):
            wind_field = wind_data.Fields[i]
            if wind_field not in field_map:
                print("No Map", wind_field)
                continue
            db_field = field_map[wind_field]
            df_tmp = pd.DataFrame(data=wind_data.Data[i], columns=[db_field])
            # print(df_tmp)
            if df.empty:
                df = df_tmp
            else:
                df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)
        #
        if len(df) == 0:
            return

        # ---Save To Database---
        columns = df.columns
        newDocuments = []
        for index, row in df.iterrows():
            newDocument = {}
            for field in columns:
                value = row[field]
                newDocument[field] = value
                if isinstance(value, float) and np.isnan(value):
                    newDocument[field] = None
            #
            newDocument["Date"] = newDocument["Announce_Date"]
            newDocument["DateTime"] = newDocument["Announce_Date"]

            if str(newDocument["Announce_Date"]) != "NaT":
                newDocument["Key2"] = newDocument["Symbol"] + "_" + Gadget.ToDateString(newDocument["Announce_Date"])
            else:
                newDocument["Key2"] = newDocument["Symbol"]
            #
            newDocuments.append(newDocument)

        #
        database.Upsert_Many("stock", "china_connect_holding", {}, newDocuments)
        print("Upsert Stock Connect @", datetime2, "#", len(newDocuments))

    #
    days = Gadget.GenerateCalenderDays(datetime1, datetime2)
    for day in days:
        Download_Stock_Connect_One_Day(database, day, "szn")
        Download_Stock_Connect_One_Day(database, day, "shn")


def Download_UnRestrict(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    field_map= {}
    field_map["wind_code"] = "Symbol" # "代码"
    field_map["sec_name"] = "Name" # "名称"
    #
    field_map["unlock_date"] = "UnRestrict_Date" # "解禁日期"
    field_map["unlock_number"] = "UnRestrict_Volume" # "解禁数量(万股)"
    field_map["unlock_type"] = "Share_Type" # "解禁股份类型"
    #
    field_map["issuedshares_afterchange"] = "Total_Shares" # "总股本"
    field_map["floatingshares_beforechange"] = "Circ_Shares_Before" # "流通A股"
    field_map["floatingshares_afterchange"] = "Circ_Shares_After" # "流通A股_1"
    field_map["percent_beforechange"] = "Circ_Ratio_Before" # "占比(%)"
    field_map["percent_afterchange"] = "Circ_Ratio_After" # "占比(%)_1"

    #
    wind_fields = []
    db_fields = []
    for wind_field, db_field in field_map.items():
        wind_fields.append(wind_field)
        db_fields.append(db_field)

    str_fields = ",".join(wind_fields)

    # ---Request Data---
    strParams = 'startdate=' + sDateTime1 +';enddate=' + sDateTime2 + ";"\
                'sectorId=a001010100000000;' \
                'search=;' \
                'field=' + str_fields
    data_restrict = w.wset("limitingtofreeofcompanydetail", strParams)

    # ---Build Dataframe---
    df = pd.DataFrame()
    for i in range(len(db_fields)):
        db_field = db_fields[i]
        df_tmp = pd.DataFrame(data=data_restrict.Data[i], columns=[db_field])
        # print(df_tmp)
        if df.empty:
            df = df_tmp
        else:
            df = pd.merge(df, df_tmp, how="left", right_index=True, left_index=True)

    #
    print(df[["Symbol", "Name", "UnRestrict_Date", "UnRestrict_Volume"]].tail(10))

    # ---Save To Database---
    columns = df.columns
    newDocuments = []
    for index, row in df.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
            if value == "":
                newDocument[field] = None
        #
        newDocument["Date"] = newDocument["UnRestrict_Date"]
        newDocument["DateTime"] = newDocument["UnRestrict_Date"]
        newDocument["Key2"] = newDocument["Symbol"] + "_" \
                           + Gadget.ToDateString(newDocument["UnRestrict_Date"])

        #
        newDocuments.append(newDocument)

    #
    database.Upsert_Many("stock", "unrestrict", {}, newDocuments)
    print("Upsert UnRestrict Info from", datetime1, "to", datetime2, "#", len(newDocuments))
    #
    return df


#
def Process_ShareHoldingChg_Planning(database, datetime1, datetime2):
    #
    # maxDate = database.ExecuteSQL("macro", "select max(date) from macro.market_factor")
    # maxDate = maxDate[0][0]
    # print("Last Update by", maxDate)
    # #
    # if datetime1 < maxDate:
    #     datetime1 = maxDate + datetime.datetime(day=1)
    #
    Download_ShareHoldingChg_Planning(database, datetime1, datetime2)
    #
    Calc_ShareHoldingChg_Planning(database, datetime1, datetime2)


# 2.股东拟增减持
# 拟增减持公告发布当日收盘价 数据范围：2018.2-今
def Download_ShareHoldingChg_Planning(database, datetime1, datetime2):
    #
    def SaveToDatabase(df):
        newDocuments = []
        for index, row in df.iterrows():
            newDocument = {}
            newDocument["Date"] = row["Date"]
            newDocument["DateTime"] = row["Date"]
            newDocument["Symbol"] = row["Symbol"]
            newDocument["Direction"] = row["Direction"]
            newDocument["Change_Upper"] = row["Change_Upper"]
            newDocument["Change_Lower"] = row["Change_Lower"]
            newDocument["ShareHolderName"] = row["ShareHolderName"]
            newDocument["StartDate"] = row["StartDate"]
            newDocument["EndDate"] = row["EndDate"]
            newDocument["Key2"] = row["Symbol"] + "_" \
                                  + row["ShareHolderName"] + "_" \
                                  + datetime.datetime.strftime(row["Date"], '%Y-%m-%d')
            #
            newDocuments.append(newDocument)
        #
        database.Upsert_Many("stock", "ShareHoldingChg_Plan", [], newDocuments)
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    # firstpublishdate 首次公告日期
    # direction 变动方向
    # changeup 拟变动数量上限
    # ShareHolderName 股东名称
    strParams = "startdate=" + sDateTime1 + ";enddate=" + sDateTime2 + \
                ";datetype=首次公告日期;type=全部;" \
                "field=windcode,firstpublishdate,direction,changeup,changelimit,ShareHolderName," \
                "StartDate,EndDate,ChangeUpPercent,ChangeLimitPercent"
    shareplan = w.wset("shareplanincreasereduce", strParams)

    if len(shareplan.Data) == 0:
        print("No Data", datetime1, datetime2)
        return

    # ---Build Dataframe---
    df_symbol = pd.DataFrame(data=shareplan.Data[0], columns=['Symbol'])
    df_publish_date = pd.DataFrame(data=shareplan.Data[1], columns=['First_Publish_Date'])
    df_direction = pd.DataFrame(data=shareplan.Data[2], columns=['Direction'])
    df_changeup = pd.DataFrame(data=shareplan.Data[3], columns=['Change_Upper'])
    df_changelimit = pd.DataFrame(data=shareplan.Data[4], columns=['Change_Lower'])
    df_shareholdername = pd.DataFrame(data=shareplan.Data[5], columns=['ShareHolder_Name'])
    df_startdate = pd.DataFrame(data=shareplan.Data[6], columns=['Start_Date'])
    df_enddate = pd.DataFrame(data=shareplan.Data[7], columns=['End_Date'])
    df_changeup_pct = pd.DataFrame(data=shareplan.Data[8], columns=['Change_Upper_Pct'])
    df_changelimit_pct = pd.DataFrame(data=shareplan.Data[9], columns=['Change_Lower_Pct'])

    #
    df_shareplan = pd.concat([df_symbol, df_publish_date, df_direction, df_changeup, df_changelimit,
                              df_shareholdername, df_startdate, df_enddate, df_changeup_pct, df_changelimit_pct], axis=1)

    df_shareplan["Change_Upper"].fillna(0, inplace=True)
    df_shareplan["Change_Lower"].fillna(0, inplace=True)
    # df_shareplan[pd.isnull(df_shareplan["startdate"])] = datetime.datetime(1999,1,1)
    # df_shareplan[pd.isnull(df_shareplan["enddate"])] = datetime.datetime(1999,1,1)

    print(df_shareplan[["Symbol", "First_Publish_Date", "Direction", "Change_Upper", "Change_Upper_Pct"]].tail(10))
    # print(df_shareplan.dtypes)
    # df_shareplan.to_csv("d:/test.csv", encoding='gbk')

    # ---Save to database---
    # SaveToDatabase(df_shareplan)
    columns = df_shareplan.columns
    newDocuments = []
    for index, row in df_shareplan.iterrows():
        newDocument = {}
        for field in columns:
            value = row[field]
            newDocument[field] = value
            if isinstance(value, float) and np.isnan(value):
                newDocument[field] = None
        #
        newDocument["Date"] = row["First_Publish_Date"]
        newDocument["DateTime"] = row["First_Publish_Date"]
        newDocument["Key2"] = row["Symbol"] + "_" \
                              + row["ShareHolder_Name"] + "_" \
                              + datetime.datetime.strftime(row["First_Publish_Date"], '%Y-%m-%d')
        #
        newDocuments.append(newDocument)
        #
    database.Upsert_Many("stock", "ShareHolding_Chg_Plan", [], newDocuments)
    #
    print("Upserted ShareHoldingChg Planning from", datetime1, "to", datetime2, "#", len(newDocuments))


def Process_ShareHoldingChg_Misc(database, datetime1, datetime2):
    #
    Download_ShareHoldingChg_Misc(database, datetime1, datetime2)
    #
    DetailsToDaily_ShareHoldingChg_Misc(database, datetime1, datetime2)


# 3.二级市场拟增减持
def Download_ShareHoldingChg_Misc(database, datetime1, datetime2):
    #
    def SaveToDatabase(df):

        # majorholderdeal_detail["ChangeValue"].fillna(np.nan, inplace=True)
        # majorholderdeal_detail["ChangeNumber"].fillna(0)
        # majorholderdeal_detail["AveragePrice"].fillna(0)
        # majorholderdeal_detail["ChangeValue"].fillna(0)

        columns = df.columns
        newDocuments = []
        for index, row in df.iterrows():
            newDocument = {}
            for field in columns:
                value = row[field]
                newDocument[field] = value
                if isinstance(value, float) and np.isnan(value):
                    newDocument[field] = None
            #
            newDocument["Date"] = row["PublishDate"]
            newDocument["DateTime"] = row["PublishDate"]
            #
            startDate = row["Start_Date"]
            if str(startDate) != "NaT":
                startDate = datetime.datetime.strftime(startDate, '%Y-%m-%d')
            else:
                startDate = "None"
            #
            endDate = row["End_Date"]
            if str(endDate) != "NaT":
                endDate = datetime.datetime.strftime(endDate, '%Y-%m-%d')
            else:
                endDate = "None"

            newDocument["Key2"] = row["Symbol"] + "_" \
                                  + row["ShareHolder_Name"] + "_" \
                                  + datetime.datetime.strftime(row["PublishDate"], '%Y-%m-%d') + "_" \
                                  + startDate + "_" \
                                  + endDate
            #
            newDocuments.append(newDocument)
        #
        database.Upsert_Many("stock", "ShareHolding_Chg_Misc", [], newDocuments)


    # 添加历史二级市场拟增减持数据
    # majorholderdeal_history = pd.read_csv('inputdata/majorholderdeal.csv',encoding='gbk',header=0)
    # majorholderdeal_history.columns = ['公告日期','二级市场拟减持','二级市场拟增持','二级市场净减持']
    # date_trans2 = list(majorholderdeal_history['公告日期'])
    # for i in range(len(date_trans2)):
    #     date_trans2[i] = datetime.datetime.strptime(date_trans2[i], '%Y-%m-%d')
    # majorholderdeal_history['公告日期'] = date_trans2
    # majorholderdeal_history.set_index(['公告日期'],inplace=True)

    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)

    # announcement_date 公告日期
    # direction
    # value_change 变动部分参考市值（万元）
    # shareholder_name
    # change_start_date 变动起始日期
    # change_end_date 变动截止日期
    # change_number 变动数量（万股）
    # average_price 交易平均价
    strParams = "startdate=" + sDateTime1 + ";enddate=" + sDateTime2 + \
                ";sectorid=a001010100000000;type=announcedate;" \
                "field=announcement_date,direction,value_change,wind_code,shareholder_name," \
                "change_start_date,change_end_date,change_number,average_price"
    majorholderdeal = w.wset("majorholderdealrecord", strParams)
    #
    if len(majorholderdeal.Data) == 0:
        print("No Data", datetime1, datetime2)
        return

    # ---Build Dataframe---
    df_announcedate = pd.DataFrame(data=majorholderdeal.Data[0], columns=['PublishDate'])
    df_direction = pd.DataFrame(data=majorholderdeal.Data[1], columns=['Direction'])
    df_value_change = pd.DataFrame(data=majorholderdeal.Data[2], columns=['Change_Value'])
    df_symbol = pd.DataFrame(data=majorholderdeal.Data[3], columns=['Symbol'])
    df_shareholdername = pd.DataFrame(data=majorholderdeal.Data[4], columns=['ShareHolder_Name'])
    df_change_start_date = pd.DataFrame(data=majorholderdeal.Data[5], columns=['Start_Date'])
    df_change_end_date = pd.DataFrame(data=majorholderdeal.Data[6], columns=['End_Date'])
    df_change_number = pd.DataFrame(data=majorholderdeal.Data[7], columns=['Change_Volume'])
    df_average_price = pd.DataFrame(data=majorholderdeal.Data[8], columns=['Price_Trade_Avg'])
    #
    majorholderdeal_detail = pd.concat([df_announcedate, df_direction, df_value_change, df_symbol,
                                        df_shareholdername, df_change_start_date, df_change_end_date, df_change_number, df_average_price], axis=1)
    #
    print(majorholderdeal_detail.tail(10))

    # majorholderdeal_detail.to_csv("d:/test.csv", encoding='gbk')
    # ---Save to database---
    SaveToDatabase(majorholderdeal_detail)

    print("Inserted ShareHoldingChg Misc from", datetime1, "to", datetime2, "#", len(majorholderdeal_detail))


def DetailsToDaily_ShareHoldingChg_Misc(database, datetime1, datetime2):
    #
    sDateTime1 = Gadget.ToDateString(datetime1)
    sDateTime2 = Gadget.ToDateString(datetime2)
    #
    filter = {}
    filter["DateTime"] = {">=": datetime1, "<=": datetime2}
    documents = database.Find("Stock", "ShareHolding_Chg_Misc", filter)
    df_shareplan = Gadget.DocumentsToDataFrame(documents)

    # ---Convert To Daily---
    # df_shareplan["Change"] = df_shareplan.apply(UpperLoewer, axis=1)
    #
    data = []
    dfGroups = df_shareplan.groupby("Date")
    for dfGroup in dfGroups:
        #
        date = dfGroup[0]
        dfGroup = dfGroup[1]
        # print(dfGroup)
        sum = dfGroup["Change_Value"].sum()
        data.append([date, sum])
    #
    df_amount = pd.DataFrame(data, columns=["Date", "Amount"])
    df_amount["Date"] = pd.to_datetime(df_amount["Date"])
    # print(df_amount.dtypes)

    # ---补齐减持为0的日子---
    tradedays = w.tdays(sDateTime1, sDateTime2, "")
    df_date = pd.DataFrame(data=tradedays.Data[0], columns=['Date'])
    # print(df_date.dtypes)
    #
    df = pd.merge(df_date, df_amount, how="left", on="Date")
    df.fillna(0)
    df.set_index('Date', inplace=True)
    print(df)

    # SaveBigFactorToDatabase(value_sold['二级市场净减持'], 'ShareHoldingChg_Misc', "market_factor")
    SaveBigFactorToDatabase(database, df['Amount'], 'ShareHoldingChg_Misc', "market_factor")


    # majorholderdeal_daily = majorholderdeal_detail.pivot_table(values='value_change', index='announcedate', columns='direction',aggfunc='sum').fillna(0)
    # majorholderdeal_daily['净减持'] = majorholderdeal_daily['减持'] - majorholderdeal_daily['增持']
    # majorholderdeal_daily.columns=['二级市场拟减持', '二级市场拟增持', '二级市场净减持']
    # #防止多次操作造成重复值，进行去重
    # index_overlapped = []
    # for i in range(len(majorholderdeal_daily.index.values)):
    #    if majorholderdeal_daily.index.values[i] in majorholderdeal_history.index.values:
    #        index_overlapped.append(majorholderdeal_daily.index.values[i])
    # majorholderdeal_history.drop(index_overlapped, axis=0, inplace=True)
    # #拼接
    # majorholderdeal_daily_total = pd.concat([majorholderdeal_history, majorholderdeal_daily], axis=0)
    # majorholderdeal_daily_total.to_csv('inputdata/majorholderdeal.csv', encoding='gbk')
    # print(majorholderdeal_daily_total)
    #
    # #1.2.3合并
    # value_sold = pd.concat([blocktrader_history, shareplan_history, majorholderdeal_history], axis=1).dropna()
    #
    # # print(value_sold)
    # SaveBigFactorToDatabase(value_sold['amount'], 'BlockTrade_Amt', "market_factor")
    # SaveBigFactorToDatabase(value_sold['rate_discount'], 'BlockTrade_Discount', "market_factor")
    # SaveBigFactorToDatabase(value_sold['二级市场净减持'], 'ShareHoldingChg_Misc', "market_factor")


def BatchDownload_BlockTrade(database):
    datetime1 = datetime.datetime(2010,2,1)
    datetime2 = datetime.datetime(2019,12,15)
    dts = Gadget.GenerateTimeRange_Monthly(datetime1, datetime2)
    for dt in dts:
        print(dt)
        Download_BlockTrade(database, dt[0], dt[1])
    pass


def BatchDownload_ShareHoldChange_Plan(database):
    datetime1 = datetime.datetime(2010,1,1)
    datetime2 = datetime.datetime(2019,12,15)
    dts = Gadget.GenerateTimeRange_Monthly(datetime1, datetime2)
    for dt in dts:
        print(dt)
        Download_ShareHoldingChg_Planning(database, dt[0], dt[1])
    pass


def BatchDownload_ShareHoldChange_Misc(database):
    datetime1 = datetime.datetime(2010,1,1)
    datetime2 = datetime.datetime(2019,12,15)
    dts = Gadget.GenerateTimeRange_Monthly(datetime1, datetime2)
    for dt in dts:
        print(dt)
        Download_ShareHoldingChg_Misc(database, dt[0], dt[1])
    pass


def Automatic_Download_Stock_Misc(database, datetime2, startEntry=0):
    #
    if datetime2 == None:
        datetime2 = datetime.datetime.today()

    #
    next_month = datetime2 + datetime.timedelta(days=30)

    # ---融资规模---
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_ipo")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_IPO(database, tmp_datetime1, tmp_datetime2)

    # 增发
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_seo")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_SEO(database, tmp_datetime1, tmp_datetime2)

    # 定向增发
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_private_seo")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_Private_SEO(database, tmp_datetime1, tmp_datetime2)

    # 可转债
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_convertible_bond")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_ConvertibleBond(database, tmp_datetime1, tmp_datetime2)

    # 配股
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_rights_issue")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_RightIssue(database, tmp_datetime1, tmp_datetime2)

    # 优先股库中暂时无数据，需要借助外部文件实现, 2020-8-24 实现接口自动化
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_preferred_stock")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_PreferredStock(database, tmp_datetime1, tmp_datetime2)
    # Load_Preferred_Stock(database, pathFilename="C:/Users/fengshimeng3/Documents/财富管理-智能投顾/inputdata/优先股基本资料.xlsx")

    # --- 解禁---
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_unrestrict")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    Download_UnRestrict(database, tmp_datetime1, tmp_datetime2)

    # ---股东拟减持---
    maxDate = database.ExecuteSQL("financial_data", "select max(date) from financial_data.stock_shareholding_chg_plan")
    maxDate = maxDate[0][0]
    tmp_datetime1 = maxDate - datetime.timedelta(days=30)
    tmp_datetime2 = datetime2 + datetime.timedelta(days=30)
    # Process_ShareHoldingChg_Planning(database, datetime1, next_month)
    Download_ShareHoldingChg_Planning(database,  tmp_datetime1, tmp_datetime2)

    # ---大宗交易---
    Download_BlockTrade_Automatic(database, datetime2)  # 更新运行这个


# 非规律基本面信息，如大宗交易，业绩预报，快报，分红数据，分析师预期数据
def Automatic_Download_Fundamental_Addtional(database, datetime2, startEntry=0):
    #
    datetime_halfYearago = datetime2 - timedelta(days=183)
    datetime_monthago = datetime2 - timedelta(days=30)
    datetime_2monthago = datetime2 - timedelta(days=30)
    datetime_temp = datetime(2020,1,1)

    #
    entry = 0
    if entry >= startEntry:
        # 按照半年频率更新
        Download_Dividend(database, datetime_halfYearago, datetime2)

    entry = 1
    if entry >= startEntry:
        DataProcess.Batch_Download_ExpressNotice(database, datetime1=datetime_temp, datetime2=datetime2, startIndex=0, logger=None)


def Transfer_Data(database):
    #
    sql = "SELECT distinct(name) FROM factor.a_sys_factor_market"
    documents = database.FindWithSQL("Factor", "", sql)
    df = Gadget.DocumentsToDataFrame(documents)
    # print(df)
    list1 = df["name"].tolist()
    #
    sql = "SELECT distinct(name) FROM factor.a_sys_factor"
    documents2 = database.FindWithSQL("Factor", "", sql)
    df2 = Gadget.DocumentsToDataFrame(documents2)
    list2 = df2["name"].tolist()
    #
    for name in list1:
        # print(name)
        documents = database.Find("Factor", "a_sys_factor", {"name": name})
        if len(documents) > 0:
            print("Factor Exist")
            continue

        documents = database.Find("Factor", "a_sys_factor_market", {"name": name})
        df = Gadget.DocumentsToDataFrame(documents)
        df["ReportDate"] = df["date"]
        df["ReleaseDate"] = df["date"]
        print(df)
        Save_Systmetic_Factor_To_Database(database, df, save_name=name, field_name="value")
        a = 0
    #
    print("*** In Database  ***")
    #
    for l in list2:
        print(l)

    for l in list1:
        if l in list2:
            print(l)


if __name__ == '__main__':
    #
    from Config import *
    pathfilename = os.getcwd() + "\..\Config\config2.json"
    config = Config.Config(pathfilename)
    database = config.DataBase("JDMySQL")
    realtime = config.RealTime()
    # pd.set_option('display.max_columns', None)
    # pd.set_option('display.max_rows', None)
    # Transfer_Data(database)

    w.start()
    datetime1 = datetime.datetime(2020, 1, 1)
    datetime2 = datetime.datetime(2020, 8, 21)
    #
    Automatic_Download_Stock_Misc(database, datetime2, startEntry=0)


    today = datetime.datetime.today()
    # begin_day = datetime.datetime(2020, 2, 1)
    base_day = datetime.datetime(2020, 4, 19)
    yesterday = base_day - datetime.timedelta(days=1)
    #
    pre_month = base_day + datetime.timedelta(days=-30)
    next_week = base_day + datetime.timedelta(days=10)
    next_month = base_day + datetime.timedelta(days=30)
    pre_week = base_day + datetime.timedelta(days=-10)

    # Test
    datetime1 = datetime.datetime(2000, 1, 1)
    datetime2 = datetime.datetime(2020, 12, 31)
    # Download_Stock_Connect_Detail(database, datetime1, datetime2)
    # Download_BondMarket_20200513(database, datetime1, datetime2)
    # Download_MoneyMarket(database, datetime1, datetime2)
    # Download_PreferredStock(database, datetime1, datetime2)

    # ---融资规模---
    # Download_IPO(database, datetime1, datetime2)
    # Download_RightIssue(database, datetime1, datetime2)
    # Download_ConvertibleBond(database, datetime1, datetime2)
    # Download_SEO(database, datetime1, datetime2)
    # Fix_SEO(database)

    # --- 解禁---
    # Download_UnRestrict(database, datetime1, next_month)

    # ---大宗交易---
    # datetime2 = base_day
    # Process_BlockTrade_Automatic(database, datetime2) # 更新运行这个

    # ---股东拟减持---
    # datetime1 = pre_month
    # datetime2 = next_month
    # Process_ShareHoldingChg_Planning(database, datetime1, datetime2)

    # 冷启动
    datetime1 = datetime.datetime(2000, 1, 1)
    datetime2 = datetime.datetime(2019, 12, 15)
    # Download_Turnover(database, datetime1, datetime2)
    # Download_MoneyMarket(database, datetime1, datetime2)
    # Download_BondMarket(database, datetime1, datetime2)
    # Download_FX(database, datetime1, datetime2)
    # Process_FX(database, datetime1, datetime2)
    # Download_CentralBankOper(database, datetime1, datetime2)
    # BatchDownload_ShareHoldChange_Plan(database) # 冷启动
    # BatchDownload_BlockTrade(database) # 冷启动

